package DAO;

import com.mysql.jdbc.JDBC4PreparedStatement;
import entity.Found;
import entity.Lost;
import utils.DBHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class LostDao {
    public ArrayList<Lost> queryByClassify(String classify) {
        ArrayList<Lost> arrayList = new ArrayList<>();
        Connection connection = DBHelper.getConnection();
        PreparedStatement pst = null;

        try {
            pst = connection.prepareStatement("SELECT * FROM lost WHERE sort = ?");
            pst.setObject(1,classify);
            System.out.println(((JDBC4PreparedStatement)pst).asSql());
            ResultSet rs = pst.executeQuery();

            while(rs.next()) {
                int lostID = rs.getInt("lostID");
                String lost_name = rs.getString("lost_name");
                String lost_time = rs.getString("lost_time");
                String describe = rs.getString("describe");
                String place = rs.getString("place");
                String sort = rs.getString("sort");
                int userID = rs.getInt("userID");

                Lost lost = new Lost(lostID,lost_name,lost_time,describe,place,sort,userID);
                arrayList.add(lost);

            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBHelper.close();
        }

        return arrayList;
    }

    public ArrayList<Lost> queryBySearch(String key) {
        ArrayList<Lost> arrayList = new ArrayList<>();
        Connection connection = DBHelper.getConnection();
        PreparedStatement pst = null;
        key = '%'+key+'%';

        try {
            pst = connection.prepareStatement("SELECT * FROM lost WHERE lost_name LIKE ? OR `describe` LIKE ?");
            pst.setObject(1,key);
            pst.setObject(2,key);
            System.out.println(((JDBC4PreparedStatement)pst).asSql());
            ResultSet rs = pst.executeQuery();

            while(rs.next()) {
                int lostID = rs.getInt("lostID");
                String lost_name = rs.getString("lost_name");
                String lost_time = rs.getString("lost_time");
                String describe = rs.getString("describe");
                String place = rs.getString("place");
                String sort = rs.getString("sort");
                int userID = rs.getInt("userID");

                Lost lost = new Lost(lostID,lost_name,lost_time,describe,place,sort,userID);
                arrayList.add(lost);

            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBHelper.close();
        }

        return arrayList;
    }

    public ArrayList<Lost> queryByID(String userid) {
        ArrayList<Lost> arrayList = new ArrayList<>();
        Connection connection = DBHelper.getConnection();
        PreparedStatement pst = null;

        try {
            pst = connection.prepareStatement("SELECT * FROM lost WHERE `userID`=?");
            pst.setObject(1,userid);
            System.out.println(((JDBC4PreparedStatement)pst).asSql());
            ResultSet rs = pst.executeQuery();

            while(rs.next()) {
                int lostID = rs.getInt("lostID");
                String lost_name = rs.getString("lost_name");
                String lost_time = rs.getString("lost_time");
                String describe = rs.getString("describe");
                String place = rs.getString("place");
                String sort = rs.getString("sort");
                int userID = rs.getInt("userID");

                Lost lost = new Lost(lostID,lost_name,lost_time,describe,place,sort,userID);
                arrayList.add(lost);

            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBHelper.close();
        }

        return arrayList;
    }

    public ArrayList<Lost> queryAll() {
        ArrayList<Lost> arrayList = new ArrayList<>();
        Connection connection = DBHelper.getConnection();
        PreparedStatement pst = null;

        try {
            pst = connection.prepareStatement("SELECT * FROM lost");
            System.out.println(((JDBC4PreparedStatement)pst).asSql());
            ResultSet rs = pst.executeQuery();

            while(rs.next()) {
                int lostID = rs.getInt("lostID");
                String lost_name = rs.getString("lost_name");
                String lost_time = rs.getString("lost_time");
                String describe = rs.getString("describe");
                String place = rs.getString("place");
                String sort = rs.getString("sort");
                int userID = rs.getInt("userID");

                Lost lost = new Lost(lostID,lost_name,lost_time,describe,place,sort,userID);
                arrayList.add(lost);

            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBHelper.close();
        }

        return arrayList;
    }

    public Lost queryByDescribeAndUserID(String describe,String UserID) {
        Lost lost = null;
        Connection connection = DBHelper.getConnection();
        PreparedStatement pst = null;

        int userID = Integer.valueOf(UserID);

        try {
            pst = connection.prepareStatement("SELECT * from `lost` WHERE `describe` = ? AND `userID` = ?");
            pst.setObject(1,describe);
            pst.setObject(2,userID);
            System.out.println(((JDBC4PreparedStatement)pst).asSql());
            ResultSet rs = pst.executeQuery();

            while(rs.next()) {
                int lostID = rs.getInt("lostID");
                String lost_goods_name = rs.getString("lost_name");
                String time = rs.getString("lost_time");
                String place = rs.getString("place");
                String sort = rs.getString("sort");

                lost = new Lost(lostID,lost_goods_name,time,describe,place,sort,userID);
                return lost;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return lost;
    }

    public Lost queryAddLost(String lost_name,String lost_time,String describe,String place,String sort,String UserID) {
        Lost lost = null;
        Connection connection = DBHelper.getConnection();
        PreparedStatement pst = null;

        int userID = Integer.valueOf(UserID);

        try {
            pst = connection.prepareStatement("INSERT INTO `lost` (`lost_name`, `lost_time`, `describe`, `place`, `sort`, `userID`) VALUES (?, ?, ?, ?, ?, ?)");
            pst.setObject(1,lost_name);
            pst.setObject(2,lost_time);
            pst.setObject(3,describe);
            pst.setObject(4,place);
            pst.setObject(5,sort);
            pst.setObject(6,userID);
            System.out.println(((JDBC4PreparedStatement)pst).asSql());
            int rs = pst.executeUpdate();

            if (rs == 1) {
                lost = new LostDao().queryByDescribeAndUserID(describe, UserID);
            }


        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBHelper.close();
        }

        return lost;
    }

    public boolean deleteLost(String LostID) {
        Lost lost = null;
        Connection connection = DBHelper.getConnection();
        PreparedStatement pst = null;

        int lostID = Integer.valueOf(LostID);
        try {
            pst = connection.prepareStatement("DELETE FROM `lost` WHERE `lostID`= ?");
            pst.setObject(1,lostID);

            System.out.println(((JDBC4PreparedStatement)pst).asSql());
            int rs = pst.executeUpdate();

            if (rs == 1) {
                return true;
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBHelper.close();
        }
        return false;
    }

    public static void main(String[] args) {
//        System.out.println(new LostDao().queryByClassify("3"));
//        System.out.println(new LostDao().queryBySearch("1"));
        System.out.println(new LostDao().queryAll());
    }
}
